﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using HelloLLLLL.Blog.Models;
using HelloLLLLL.Blog.DAL;
using HelloLLLLL.Blog.Common;
using Newtonsoft.Json;

namespace HelloLLLLL.Blog.html
{
    public partial class Index : System.Web.UI.Page
    {
        public UserInfo LLuser = null;
        DBHelper helper = new DBHelper();
        public List<Banner> Banners;
        public List<string> ListNotice = new List<string>();
        public List<LLArticle> ListTops = null;
        public List<LLArticle> ListNews = new List<LLArticle>();
        public List<LLArticle> ListHots =null;
        public List<LeaveMessage> listAllPL = null;
        public string[] Wea;
        protected void Page_Load(object sender, EventArgs e)
        {
          
                List<UserInfo> list = new ModelHandler<UserInfo>().FillModel(helper.GetDataTable("  select top 1 *  from userInfo", null));

                LLuser = list[0];

            GetBanner();
            GetNotice();
            GetTopArticle();
            GetNewArticle();
            GetHotArticle();
            GetWeather();
            GetComments();
        }

        private void GetBanner()
        {
            
                Banners = new ModelHandler<Banner>().FillModel(helper.GetDataTable("  select top 2 * from [Banner]  where banIsShow=1 order by BanSort asc", null));
        }

        private void GetNotice()
        {
           
                var tb = helper.GetDataTable("select top 3 Title from [Notice] order by id desc ", null);

                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    ListNotice.Add(tb.Rows[i][0].ToString());
                }

                
          
          
        }

        private void GetTopArticle()
        {
            //      string sql = @"  select * from LLArticle where IstOP=1 
            //union all
            //select [Id]
            //    ,[Title]
            //    ,[HomeImg]
            //    ,[Digest]
            //    ,[ArtTime]
            //    ,[LastUpdatetime]
            //    ,[Content]
            //    ,[ReadPwd]
            //    ,[Editor]
            //    ,[PresentCount]
            //    ,[CommentCount]
            //    ,[ReadCount]
            //    ,[IsTop] from(select * ,row_number()over(order by ReadCount desc) as num from LLArticle ) as t
            //where t.num<= (SELECT 2-COUNT(1) FROM LLArticle  WHERE IstOP =1)";

            string sql = @"select top 2  * from LLArticle where IstOP=1  order by id desc";
            ListTops = new ModelHandler<LLArticle>().FillModel(helper.GetDataTable(sql, null));
            if (ListTops == null)
            {
                return;
            }
            foreach (var ar in ListTops)
            {
                ar.ArtType = new List<int>();
                ar.ArtTypeName = new List<string>();
                string sql2 = "select a.TypeId ,b.TypeName from ArtiAndType a join LLArtType b on a.TypeId=b.Id where a.ArtId=" + ar.Id;
                var dt = helper.GetDataTable(sql2, null);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ar.ArtType.Add(Convert.ToInt32(dt.Rows[i][0]));
                    ar.ArtTypeName.Add(dt.Rows[i][1].ToString());
                }


            }

        }

        private void GetNewArticle()
        {
            string ids = "";
            if (ListTops != null)
                ids = string.Join(",", ListTops.Select(a => a.Id));
            if (ids == "")
                ids = "0";

            string sql = @"select top 11 * from LLArticle where Id not in (" + ids + ") order by id desc ";

            ListNews = new ModelHandler<LLArticle>().FillModel(helper.GetDataTable(sql, null));
            if (ListNews == null)
            {
                return;
            }
            foreach (var ar in ListNews)
            {
                ar.ArtType = new List<int>();
                ar.ArtTypeName = new List<string>();
                string sql2 = "select a.TypeId ,b.TypeName from ArtiAndType a join LLArtType b on a.TypeId=b.Id where a.ArtId=" + ar.Id;
                var dt = helper.GetDataTable(sql2, null);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ar.ArtType.Add(Convert.ToInt32(dt.Rows[i][0]));
                    ar.ArtTypeName.Add(dt.Rows[i][1].ToString());
                }


            }
        }

        private void GetHotArticle()
        {
            string ids = "";
            string ids2 = "";
            if (ListTops != null)
                ids = string.Join(",", ListTops.Select(a => a.Id));
            if (ListNews != null)
                 ids2= string.Join(",", ListNews.Select(a => a.Id));
            if (ids != "" && ids2 != "")
            {
                ids += "," + ids2;
            }
            if (ids == "")
            {
                ids = ids2;
            }
            if (ids == "")
            {
                ids = "0";
            }


            string sql = "select top 8 * from LLArticle where Id not in (" + ids + ") order by readcount desc ";

            ListHots = new ModelHandler<LLArticle>().FillModel(helper.GetDataTable(sql, null));

            if (ListHots == null || ListHots.Count == 0)
            {
                sql = "select top 8 * from LLArticle order by readcount desc ";

                ListHots = new ModelHandler<LLArticle>().FillModel(helper.GetDataTable(sql, null));
            }

            if (ListHots == null)
            {
                return;
            }

            foreach (var ar in ListHots)
            {
                ar.ArtType = new List<int>();
                ar.ArtTypeName = new List<string>();
                string sql2 = "select a.TypeId ,b.TypeName from ArtiAndType a join LLArtType b on a.TypeId=b.Id where a.ArtId=" + ar.Id;
                var dt = helper.GetDataTable(sql2, null);
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    ar.ArtType.Add(Convert.ToInt32(dt.Rows[i][0]));
                    ar.ArtTypeName.Add(dt.Rows[i][1].ToString());
                }


            }

        }

        private void GetWeather()
        {
            WeatherService.WeatherWebService w = new WeatherService.WeatherWebService();
            Wea = new string[23];
            string loginip = HelloLLLLL.Blog.Common.QQWryLocator.GetIPAddress(Request);
            if (loginip == "::1")
            {
                loginip = "127.0.0.1";
            }
            
            string result = HelloLLLLL.Blog.Common.QQWryLocator.GetstringIpAddress(loginip);
            LLIP ipInfo = null;
            if (!string.IsNullOrWhiteSpace(result) && result != string.Empty)
            {
                ipInfo = JsonConvert.DeserializeObject<LLIP>(result);
            }
            if (ipInfo!=null&&string.IsNullOrWhiteSpace(ipInfo.city))
            {
                Wea = w.getWeatherbyCityName("重庆");
            }
            else if(ipInfo!=null&& !string.IsNullOrWhiteSpace(ipInfo.city))
            {
                int m = -1;
                if ((m=ipInfo.city.LastIndexOf('市')) != -1)
                {
                    ipInfo.city=ipInfo.city.Substring(0, ipInfo.city.Length-1);
                }

                Wea = w.getWeatherbyCityName(ipInfo.city);
            }
            else
            {
                Wea = w.getWeatherbyCityName("重庆");
            }

        }
   
        private void GetComments()
        {

             listAllPL = new ModelHandler<LeaveMessage>().FillModel(helper.GetDataTable("select top 20 * from LeaveMessage order by id desc", null));

        }
    
    }
    


}